<?php
/*
 * Created on Feb 19, 2010
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */
class ReesNewModel{	
	
	function ReesNewModel(){
	
	}
	
	function getReesById($id = null){
		global $connDB;  //The database connection
		if($id == null) return null;
		$sql = "SELECT * FROM ".TBL_ITEM." WHERE id = '$id' AND deleteFlag = 0";
		$result = $connDB->DB_query($sql,$connDB->connection);		
		return $connDB->DB_fetch_array($result);
	}
	
	function incrementShow($id = null){
		global $connDB;  //The database connection	
		if(!isset($id)) return;
		$sql = "UPDATE ".TBL_ITEM." SET showNo = showNo+1 WHERE id = $id";  
		$connDB->DB_query($sql, $connDB->connection);
	}
	
	function addNewItem($arrayData = null){
		global $connDB;  //The database connection
		$createDate = FormatDateForSQL($arrayData['createDate']).' '.date('h:i:s');
		$endDate = '';
		if(!isset($arrayData['endDate']) || empty($arrayData['endDate'])){
			$incermentDate = "+".MAX_DATE_REES_DISPLAY." day";
			$date = strtotime($incermentDate, strtotime(FormatDateForSQL($arrayData['createDate'])));
			$endDate = date("Y-m-d", $date).' '.date('h:i:s');
		}else{
			$endDate = FormatDateForSQL($arrayData['endDate']).' '.date('h:i:s');
		}
		$sql = "INSERT INTO ".TBL_ITEM." (".
					" rees_type_id,".
					" rees_cityregion_id,".
					" rees_transaction_id,".
					" rees_city_id,".
					" rees_district_id,".
					" rees_user_id,".
					" title,".
					" description,".
					" content,".
					" contact,".
					" place,".
					" source,".
					" direction,".
					" size,".
					" price,".
					" isSpecial,".
					" endDate,".
					" createDate,".
					" updateDate) ".
				"VALUES (" . $arrayData['typehouse'] . ",
					" . $arrayData['region'] . ",
					" . $arrayData['reestype'] . ",
					" . $arrayData['city'] . ",
					" . $arrayData['district'] . ",
					" . $arrayData['userId'] . ",
					'" . $arrayData['title'] . "',
					'" . $arrayData['description'] . "',
					'" . $arrayData['content'] . "',
					'" . $arrayData['contact'] . "',
					'" . $arrayData['address'] . "',
					'" . $arrayData['source'] . "',
					'" . $arrayData['direction'] . "',
					'" . $arrayData['size'] . "',
					'" . $arrayData['price'] . "',
					'" . $arrayData['isSpecial'] . "',
					'" . $endDate . "',
					'" . $createDate . "',
					'" . $createDate . "')";
		$msg = _('Bất động sản <') . ' ' . $arrayData['title'] . ' ' . _('> đã thêm vào hệ thống');
		$ErrMsg = _('Thao tác trên database bị lỗi xảy ra');
		$DbgMsg = _('Lệnh cập nhật database bị lỗi');
		$result = $connDB->DB_query($sql, $connDB->connection, $ErrMsg, $DbgMsg);
		if($result){
			return 0;
		}else{
			return 2;
		}
	}
	
	function editItem($id = null, $arrayData = null){
		global $connDB;  //The database connection
		if(!isset($id)) return 2;			
		$endDate = FormatDateForSQL($arrayData['endDate']).' '.date('h:i:s');
		$sql = "UPDATE ".TBL_ITEM." SET ".
					" rees_type_id=" . $arrayData['typehouse'] . "," .
					" rees_cityregion_id=" . $arrayData['region'] . "," .
					" rees_transaction_id=" . $arrayData['reestype'] . "," .
					" rees_city_id=" . $arrayData['city'] . "," .
					" rees_district_id=" . $arrayData['district'] . "," .
					" rees_user_id=" . $arrayData['userId'] . "," .
					" title='" . $arrayData['title'] . "'," .
					" description='" . $arrayData['description'] . "'," .
					" content='" . $arrayData['content'] . "'," .
					" contact='" . $arrayData['contact'] . "'," .
					" place='" . $arrayData['address'] . "'," .
					" direction='" . $arrayData['direction'] . "'," .
					" size='" . $arrayData['size'] . "'," .
					" size='" . $arrayData['size'] . "'," .
					" price='" . $arrayData['price'] . "'," .
					" isSpecial='" . $arrayData['isSpecial'] . "'," .
					" endDate='" . $endDate . "'," .					
					"updateDate='" . date('Y-m-d h:i:s') . "' " .
				" WHERE id = '$id'";	
		$msg = _('Bất động sản <') . ' ' . $arrayData['title'] . ' ' . _('> đã thêm vào hệ thống');
		$ErrMsg = _('Thao tác trên database bị lỗi xảy ra');
		$DbgMsg = _('Lệnh cập nhật database bị lỗi');
		$result = $connDB->DB_query($sql, $connDB->connection, $ErrMsg, $DbgMsg);
		if($result){
			return 0;
		}else{
			return 2;
		}
	}
	
	function getArrayNameRees($resultSet = null){
		global $connDB;  //The database connection
		if($resultSet == null) return null;
		return $connDB->DB_fetch_array($resultSet);
	}
		
	function search($arrayData = null, &$num = 0, $offset = null, $limit = null){
		global $connDB;  //The database connection
		$arrayWhere = array();
		if (isset($arrayData['title']) AND strlen($arrayData['title'])>0) {
			$arrayWhere[] = 'title LIKE \'%'. $arrayData['title'] .'%\'';  
		}
		if (isset($arrayData['address']) AND strlen($arrayData['address'])>0) {
			$arrayWhere[] = 'place LIKE \'%'. $arrayData['address'] .'%\'';  
		}				
		if (isset($arrayData['direction']) AND strlen($arrayData['direction'])>0) {
			$arrayWhere[] = 'direction LIKE \'%'. $arrayData['direction'] .'%\'';  
		}		
		if (isset($arrayData['reestype']) AND $arrayData['reestype']>0) {
			$arrayWhere[] = 'rees_transaction_id = '. $arrayData['reestype'];  
		}
		if (isset($arrayData['typehouse']) AND $arrayData['typehouse']>0) {
			$arrayWhere[] = 'rees_type_id = '. $arrayData['typehouse'];  
		}
		if (isset($arrayData['region']) AND $arrayData['region']>0) {
			$arrayWhere[] = 'rees_cityregion_id = '. $arrayData['region'];  
		}
		if (isset($arrayData['city']) AND $arrayData['city']>0) {
			$arrayWhere[] = 'rees_city_id = '. $arrayData['city'];  
		}
		if (isset($arrayData['district']) AND $arrayData['district']>0) {
			$arrayWhere[] = 'rees_district_id = '. $arrayData['district'];  
		}
		if (isset($arrayData['price']) AND strlen($arrayData['price'])>0) {
			$arrayWhere[] = 'price LIKE \'%'. $arrayData['price'] .'%\'';  
		}
		if (isset($arrayData['size']) AND strlen($arrayData['size'])>0) {
			$arrayWhere[] = 'size LIKE \'%'. $arrayData['size'] .'%\'';  
		}
		if (isset($arrayData['description']) AND strlen($arrayData['description'])>0) {
			$arrayWhere[] = 'description LIKE \'%'. $arrayData['description'] .'%\'';  
		}
		$stringWhere = '';
		foreach($arrayWhere as $where){
			$stringWhere .= $where.' AND ';
		}
		$sql =	" SELECT * FROM ".TBL_ITEM.
				" WHERE ". $stringWhere ."deleteFlag = 0";
		$s = $sql;
		$result = $connDB->DB_query($sql,$connDB->connection);
		$num = 	$connDB->DB_num_rows($result);
		$sql =	" SELECT * FROM ".TBL_ITEM.
				" WHERE ". $stringWhere ."deleteFlag = 0".
				" ORDER BY updateDate DESC, title ASC ".getLimitFormat($offset, $limit);
		$result = $connDB->DB_query($sql,$connDB->connection);
      	return $result;
	}
	
	function searchAdmin($arrayData = null, &$num = 0, $offset = null, $limit = null){
		global $connDB;  //The database connection
		$arrayWhere = array();
		if (isset($arrayData['title']) AND strlen($arrayData['title'])>0) {
			$arrayWhere[] = 'title LIKE \'%'. $arrayData['title'] .'%\'';  
		}
		if (isset($arrayData['address']) AND strlen($arrayData['address'])>0) {
			$arrayWhere[] = 'place LIKE \'%'. $arrayData['address'] .'%\'';  
		}				
		if (isset($arrayData['direction']) AND strlen($arrayData['direction'])>0) {
			$arrayWhere[] = 'direction LIKE \'%'. $arrayData['direction'] .'%\'';  
		}		
		if (isset($arrayData['reestype']) AND $arrayData['reestype']>0) {
			$arrayWhere[] = 'rees_transaction_id = '. $arrayData['reestype'];  
		}
		if (isset($arrayData['typehouse']) AND $arrayData['typehouse']>0) {
			$arrayWhere[] = 'rees_type_id = '. $arrayData['typehouse'];  
		}
		if (isset($arrayData['region']) AND $arrayData['region']>0) {
			$arrayWhere[] = 'rees_cityregion_id = '. $arrayData['region'];  
		}
		if (isset($arrayData['city']) AND $arrayData['city']>0) {
			$arrayWhere[] = 'rees_city_id = '. $arrayData['city'];  
		}
		if (isset($arrayData['district']) AND $arrayData['district']>0) {
			$arrayWhere[] = 'rees_district_id = '. $arrayData['district'];  
		}
		if (isset($arrayData['price']) AND strlen($arrayData['price'])>0) {
			$arrayWhere[] = 'price LIKE \'%'. $arrayData['price'] .'%\'';  
		}
		if (isset($arrayData['size']) AND strlen($arrayData['size'])>0) {
			$arrayWhere[] = 'size LIKE \'%'. $arrayData['size'] .'%\'';  
		}
		if (isset($arrayData['description']) AND strlen($arrayData['description'])>0) {
			$arrayWhere[] = 'description LIKE \'%'. $arrayData['description'] .'%\'';  
		}
		if (isset($arrayData['source']) AND strlen($arrayData['source'])>0) {
			$arrayWhere[] = 'source LIKE \'%'. $arrayData['source'] .'%\'';  
		}
		if (isset($arrayData['content']) AND strlen($arrayData['content'])>0) {
			$arrayWhere[] = 'content LIKE \'%'. $arrayData['content'] .'%\'';  
		}
		if (isset($arrayData['contact']) AND strlen($arrayData['contact'])>0) {
			$arrayWhere[] = 'contact LIKE \'%'. $arrayData['contact'] .'%\'';  
		}
		if (isset($arrayData['createFromDate'])) {
			$arrayWhere[] = 'DATE_FORMAT(updateDate, \'%Y/%m/%d\') >= \''.FormatDateForSQL($arrayData['createFromDate']).'\'';  
		}
		if (isset($arrayData['createToDate'])) {
			$arrayWhere[] = 'DATE_FORMAT(updateDate, \'%Y/%m/%d\') <= \''.FormatDateForSQL($arrayData['createToDate']).'\'';  
		}	
		$stringWhere = '';
		foreach($arrayWhere as $where){
			$stringWhere .= $where.' AND ';
		}
		$sql =	" SELECT * FROM ".TBL_ITEM.
				" WHERE ". $stringWhere ."deleteFlag = 0";
		$result = $connDB->DB_query($sql,$connDB->connection);
		$num = 	$connDB->DB_num_rows($result);
		$sql =	" SELECT * FROM ".TBL_ITEM.
				" WHERE ". $stringWhere ."deleteFlag = 0".
				" ORDER BY updateDate DESC, title ASC ".getLimitFormat($offset, $limit);
		$result = $connDB->DB_query($sql,$connDB->connection);
      	return $result;
	}
	
	function getCountItems(){		
		global $connDB;  //The database connection
		$sql =	"SELECT item.*, city.`name` as city_name, district.`name` as district_name  ".
				"FROM ". TBL_ITEM .' item '.
				"LEFT JOIN ". TBL_CITY ." city ON item.`rees_city_id` = city.`id` ".
				"LEFT JOIN ". TBL_DISTRICT ." district ON item.`rees_district_id` = district.`id` ".
				" WHERE item.deleteFlag = 0 ".
				" ORDER BY item.updateDate DESC, item.title ASC ";
		$result = $connDB->DB_query($sql,$connDB->connection);
      	return $connDB->DB_num_rows($result);
	}
	
	function getTopItems($offset = null, $limit = null){
		global $connDB;  //The database connection
		$sql =	"SELECT item.*, city.`name` as city_name, district.`name` as district_name  ".
				"FROM ". TBL_ITEM .' item '.
				"LEFT JOIN ". TBL_CITY ." city ON item.`rees_city_id` = city.`id` ".
				"LEFT JOIN ". TBL_DISTRICT ." district ON item.`rees_district_id` = district.`id` ".
				" WHERE item.deleteFlag = 0 ".
				" ORDER BY item.updateDate DESC, item.title ASC ".getLimitFormat($offset, $limit);
		$result = $connDB->DB_query($sql,$connDB->connection);
      	return $result;
	}
	
	function getArrayNameItem($resultSet = null){
		global $connDB;  //The database connection
		if($resultSet == null) return null;
		return $connDB->DB_fetch_array($resultSet);
	}
	
	function deleteRees($id){
		global $connDB;  //The database connection
		$sql = "UPDATE ".TBL_ITEM." SET deleteFlag=" . 1 . ",
						updateDate='" . date('Y-m-d h:i:s') . "'
				WHERE id = '$id'";			
		$msg = _('Bất động sản <') . ' ' . $id . ' ' . _('> xóa');
		$ErrMsg = _('Thao tác trên database bị lỗi xảy ra');
		$DbgMsg = _('Lệnh cập nhật database bị lỗi');
		$result = $connDB->DB_query($sql, $connDB->connection, $ErrMsg, $DbgMsg);
		if($result){
			return 0;
		}else{
			return 2;
		}
	}
	
	function dataSeekResult(&$result, $index = 0){
		global $connDB;  //The database connection
		$connDB->DB_data_seek($result, $index);
	}
	
	function updateImageField($id = null, $value = null){
		global $connDB;  //The database connection
		if($id == null || $value == null) return null;
		$sql = "UPDATE ".TBL_ITEM." SET image = '$value' WHERE id = $id";
      	return $connDB->DB_query($sql,$connDB->connection);
	}
};

/* Create cityregion controller object */
$reesNewModel = new ReesNewModel;
?>